<!DOCTYPE html>
<html>
  <head>
    <meta charset="utf-8" >

<title>定时备份pg库上传oss并发送通知 | 跬步</title>
<meta name="description" content="不积跬步，无以至千里">

<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">

<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.2/css/all.css" integrity="sha384-fnmOCqbTlWIlj8LyTjo7mOUStjsKC4pOpQbqyi7RrhN7udi9RwhKkMHpvLbHG9Sr" crossorigin="anonymous">
<link rel="shortcut icon" href="https://skylaugh.gitee.io/blog/favicon.ico?v=1698591240151">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/KaTeX/0.10.0/katex.min.css">
<link rel="stylesheet" href="https://skylaugh.gitee.io/blog/styles/main.css">



<script src="https://cdn.jsdelivr.net/npm/vue/dist/vue.js"></script>
<script src="https://cdn.bootcss.com/highlight.js/9.12.0/highlight.min.js"></script>

<link rel="stylesheet" href="https://unpkg.com/aos@next/dist/aos.css" />



  </head>
  <body>
    <div id="app" class="main">

      <div class="sidebar" :class="{ 'full-height': menuVisible }">
  <div class="top-container" data-aos="fade-right">
    <div class="top-header-container">
      <a class="site-title-container" href="https://skylaugh.gitee.io/blog">
        <img src="https://skylaugh.gitee.io/blog/images/avatar.png?v=1698591240151" class="site-logo">
        <h1 class="site-title">跬步</h1>
      </a>
      <div class="menu-btn" @click="menuVisible = !menuVisible">
        <div class="line"></div>
      </div>
    </div>
    <div>
      
        
          <a href="/blog/archives" class="site-nav">
            归档
          </a>
        
      
        
          <a href="/blog/tags" class="site-nav">
            标签
          </a>
        
      
        
          <a href="/blog/post/about" class="site-nav">
            关于
          </a>
        
      
    </div>
  </div>
  <div class="bottom-container" data-aos="flip-up" data-aos-offset="0">
    <div class="social-container">
      
        
      
        
      
        
      
        
      
        
      
    </div>
    <div class="site-description">
      不积跬步，无以至千里
    </div>
    <div class="site-footer">
      <a href="mailto:skylaugh@foxmail.com">Email</a> | <a class="rss" href="https://skylaugh.gitee.io/blog/atom.xml" target="_blank">RSS</a>
    </div>
  </div>
</div>


      <div class="main-container">
        <div class="content-container" data-aos="fade-up">
          <div class="post-detail">
            <h2 class="post-title">定时备份pg库上传oss并发送通知</h2>
            <div class="post-date">2023-07-29</div>
            
              <div class="feature-container" style="background-image: url('https://skylaugh.gitee.io/blog/post-images/ding-shi-bei-fen-pg-ku-shang-chuan-oss-bing-fa-song-tong-zhi.jpg')">
              </div>
            
            <div class="post-content">
              <p>先授权备份文件存储目录的权限给postgres用户</p>
<pre><code>chown /data/backup 
chmod 700 /data/backup
</code></pre>
<p>下载ossutil工具，并授权</p>
<pre><code>wget https://gosspublic.alicdn.com/ossutil/1.7.17/ossutil64 
chmod 755 ossutil64
# 配置ak,sk,region endpoint的部分省略
</code></pre>
<p>编写脚本</p>
<pre><code>#!/bin/bash 
BACKUP_DIR=/data/backup/pg 
BUCKET_NAME=pg-data-backup 
OSS_DIR=pg_203 

# 数据库列表 
DATABASES=( 
mattermost 
n8n 
devops 
superset 
temporal
temporal_visibility
keycloak 
fisheye 
grafana
harbor 
outline 
postgres
template1
template0 
)

# 循环备份每个数据库 
for DB in &quot;${DATABASES[@]}&quot;; do 
    FILE_NAME=&quot;${DB}_$(date +%Y%m%d).sql&quot; 
    
    # 创建数据库名作为子目录 
    DB_BACKUP_DIR=&quot;${BACKUP_DIR}/${DB}&quot; 
    mkdir -p &quot;${DB_BACKUP_DIR}&quot; 
    
    # 为每个数据库创建备份 
    pg_dump $DB &gt; &quot;${DB_BACKUP_DIR}/${FILE_NAME}&quot; 
    
    # 将备份文件压缩 
    tar -czf &quot;${DB_BACKUP_DIR}/${FILE_NAME}.tar.gz&quot; -C &quot;${DB_BACKUP_DIR}&quot; &quot;${FILE_NAME}&quot; 
    
    # 上传压缩后的备份到oss 
    /data/backup/ossutil64 cp &quot;${DB_BACKUP_DIR}/${FILE_NAME}.tar.gz&quot; oss://${BUCKET_NAME}/${OSS_DIR}/${DB}/${FILE_NAME}.tar.gz 
    echo ${DB}'备份完成' 
    done 
    
# 发送钉钉 curl 'https://oapi.dingtalk.com/robot/send?access_token=xxxxx' \ -H 'Content-Type: application/json' \ -d &quot;{\&quot;msgtype\&quot;: \&quot;text\&quot;,\&quot;text\&quot;: {\&quot;content\&quot;:\&quot;$OSS_DIR已备份完成\&quot;}}&quot;
</code></pre>
<p>设置定时执行</p>
<pre><code>crontab -e 
0 1 * * * /bin/bash /data/backup/pg_backup.sh
</code></pre>
<p>上述脚本中没有用 <code>pg_dumpall</code> 是因为有两个库的数据量特别大，但不是重要数据，所以没有用全库备份的方式，用以下sql可以查询pg库各库的大小</p>
<pre><code>SELECT pg_database.datname AS database_name, pg_size_pretty(pg_database_size(pg_database.datname)) AS database_size FROM pg_database 
order by pg_database_size(pg_database.datname) desc;
</code></pre>

            </div>
            
              <div class="tag-container">
                
                  <a href="https://skylaugh.gitee.io/blog/tag/6maU16wcA/" class="tag">
                    数据库
                  </a>
                
              </div>
            
            
              <div class="next-post">
                <div class="next">下一篇</div>
                <a href="https://skylaugh.gitee.io/blog/post/kai-yuan-xie-yi/">
                  <h3 class="post-title">
                    开源协议
                  </h3>
                </a>
              </div>
            

            

          </div>

        </div>
      </div>
    </div>

    <script src="https://unpkg.com/aos@next/dist/aos.js"></script>

<script type="application/javascript">

AOS.init();

hljs.initHighlightingOnLoad()

var app = new Vue({
  el: '#app',
  data: {
    menuVisible: false,
  },
})

</script>




  </body>
</html>
